{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "import xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "this_dir = Path('.').resolve()\n",
    "paths = (this_dir / 'sales_data').rglob('*.xls*')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\April.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\August.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\December.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\February.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\January.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\July.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\June.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\March.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\May.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\November.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\October.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\existing\\September.xls\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\April.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\August.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\December.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\February.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\January.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\July.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\June.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\March.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\May.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\November.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\October.xlsx\n",
      "正在读取 E:\\dev\\Merge-excel-files\\sales_data\\new\\September.xlsx\n",
      "excel 文件已全部读取完成。\n"
     ]
    }
   ],
   "source": [
    "parts = []\n",
    "for path in paths:\n",
    "    print(f'正在读取 {path}')\n",
    "    part = pd.read_excel(path, index_col='transaction_id')\n",
    "    parts.append(part)\n",
    "print('excel 文件已全部读取完成。')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>store</th>\n",
       "      <th>transaction_date</th>\n",
       "      <th>plan</th>\n",
       "      <th>contract_type</th>\n",
       "      <th>amount</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>transaction_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>70ff4cf0</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>Bronze</td>\n",
       "      <td>EXISTING</td>\n",
       "      <td>12.20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0967828d</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>Gold</td>\n",
       "      <td>EXISTING</td>\n",
       "      <td>19.35</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d9dd259f</th>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>Bronze</td>\n",
       "      <td>EXISTING</td>\n",
       "      <td>12.20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>eff0c296</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>Silver</td>\n",
       "      <td>EXISTING</td>\n",
       "      <td>14.25</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2a72bd73</th>\n",
       "      <td>Boston</td>\n",
       "      <td>2019-04-01</td>\n",
       "      <td>Bronze</td>\n",
       "      <td>EXISTING</td>\n",
       "      <td>12.20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53a449b0</th>\n",
       "      <td>New York</td>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>Silver</td>\n",
       "      <td>NEW</td>\n",
       "      <td>14.25</td>\n",
       "      <td>ACTIVE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35b46c26</th>\n",
       "      <td>Boston</td>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>Silver</td>\n",
       "      <td>NEW</td>\n",
       "      <td>14.25</td>\n",
       "      <td>ACTIVE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9e498c85</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>Silver</td>\n",
       "      <td>NEW</td>\n",
       "      <td>14.25</td>\n",
       "      <td>ACTIVE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5841d16e</th>\n",
       "      <td>San Francisco</td>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>Silver</td>\n",
       "      <td>NEW</td>\n",
       "      <td>14.25</td>\n",
       "      <td>ACTIVE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86937799</th>\n",
       "      <td>Chicago</td>\n",
       "      <td>2019-09-30</td>\n",
       "      <td>Gold</td>\n",
       "      <td>NEW</td>\n",
       "      <td>19.35</td>\n",
       "      <td>ACTIVE</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160851 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        store transaction_date    plan contract_type  amount  \\\n",
       "transaction_id                                                                 \n",
       "70ff4cf0        San Francisco       2019-04-01  Bronze      EXISTING   12.20   \n",
       "0967828d        San Francisco       2019-04-01    Gold      EXISTING   19.35   \n",
       "d9dd259f            Las Vegas       2019-04-01  Bronze      EXISTING   12.20   \n",
       "eff0c296              Chicago       2019-04-01  Silver      EXISTING   14.25   \n",
       "2a72bd73               Boston       2019-04-01  Bronze      EXISTING   12.20   \n",
       "...                       ...              ...     ...           ...     ...   \n",
       "53a449b0             New York       2019-09-30  Silver           NEW   14.25   \n",
       "35b46c26               Boston       2019-09-30  Silver           NEW   14.25   \n",
       "9e498c85              Chicago       2019-09-30  Silver           NEW   14.25   \n",
       "5841d16e        San Francisco       2019-09-30  Silver           NEW   14.25   \n",
       "86937799              Chicago       2019-09-30    Gold           NEW   19.35   \n",
       "\n",
       "                status  \n",
       "transaction_id          \n",
       "70ff4cf0           NaN  \n",
       "0967828d           NaN  \n",
       "d9dd259f           NaN  \n",
       "eff0c296           NaN  \n",
       "2a72bd73           NaN  \n",
       "...                ...  \n",
       "53a449b0        ACTIVE  \n",
       "35b46c26        ACTIVE  \n",
       "9e498c85        ACTIVE  \n",
       "5841d16e        ACTIVE  \n",
       "86937799        ACTIVE  \n",
       "\n",
       "[160851 rows x 6 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_data = pd.concat(parts)\n",
    "all_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "生成新的 excel 文件 output.xlsx\n"
     ]
    }
   ],
   "source": [
    "outf = 'output.xlsx'\n",
    "all_data.to_excel(outf, 'detail')\n",
    "print(f'生成新的 excel 文件 {outf}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "对新的 excel 文件 output.xlsx 进行了调整行宽以适应内容。\n"
     ]
    }
   ],
   "source": [
    "app = xw.App(visible=False, add_book=False)\n",
    "book = app.books.open(outf)\n",
    "sheet = book.sheets['detail']\n",
    "sheet.autofit()\n",
    "book.save()\n",
    "book.close()\n",
    "app.kill()\n",
    "print(f'对新的 excel 文件 {outf} 进行了调整行宽以适应内容。')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mergefs",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
